﻿using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Wise.Util;

namespace Wise.Entities
{
    [JsonObject(MemberSerialization.OptIn)]
    public class 表映射 : _MyMap
    {
        public 表映射()
        {
        }

        private Guid _id = Guid.NewGuid();
        [JsonProperty]
        public Guid Id
        {
            get { return _id; }
            private set
            {
                _id = value;
                if (_id == Guid.Empty)
                    _id = Guid.NewGuid();
            }
        }

        [JsonProperty]
        public bool 测试 { get; set; }

        [JsonProperty]
        public TableExistsActionType 表存在动作 { get; set; }

        string _源表名 = string.Empty;
        [JsonProperty]
        public string 源表名
        {
            get
            {
                return _源表名;
            }
            set
            {
                _源表名 = value?.Trim();
                OnPropertyChange(nameof(this.源表名));
            }
        }


        [JsonProperty]
        public string 源表匹配项 { get; set; }

        string _目标表名 = String.Empty;
        [JsonProperty]
        public string 目标表名
        {
            get
            {
                return _目标表名;
            }
            set
            {
                _目标表名 = value?.Trim();
                OnPropertyChange(nameof(this.目标表名));
            }
        }

        [JsonProperty]
        public string 目标表匹配项 { get; set; }

        List<字段映射> _字段映射 = new List<字段映射>();

        /// <summary>
        /// 字段映射 永不为null
        /// </summary>
        [JsonProperty]
        public List<字段映射> 字段映射
        {
            get
            {
                return _字段映射;
            }
            set
            {
                _字段映射 = value;
                if (_字段映射 == null)
                    _字段映射 = new List<Entities.字段映射>();
            }
        }


        RowConditionalType _同步条件 = 0;
        [JsonProperty]
        public RowConditionalType 同步条件
        {
            get
            {
                return _同步条件;
            }
            set
            {
                _同步条件 = value;
                OnPropertyChange(nameof(this.同步条件));
            }
        }

        /// <summary>
        /// 源表的真实结构，用于计算
        /// </summary>
        [NonSerialized]
        public 表结构 _源表 = null;

        /// <summary>
        /// 目标表的真实结构，用于计算
        /// </summary>
        [NonSerialized]
        public 表结构 _目标表 = null;

        /// <summary>
        /// 真实映射，用于计算
        /// </summary>
        [NonSerialized]
        public List<MyTuple<表的列, 表的列, 字段映射>> _Mapping = null;



        public static 表结构 Create表结构(string _TableName, IDbConnection Connection)
        {

            string _QUERY_TABLE = null;
            #region 查询语句
            {
                _QUERY_TABLE = @"
SELECT COUNT(*) FROM sys.[tables]
WHERE[name] = @TableName

SELECT sep.[name] AS 属性名,sep.[value] AS 属性值
FROM  sys.extended_properties sep 
WHERE sep.[major_id]=OBJECT_ID(@TableName)
AND [sep].[minor_id]=0
ORDER BY sep.[value]

SELECT
    [id].[name] AS [索引名称],[id].[type] AS [索引类型],[id].[is_unique] AS [唯一索引],
    [id].[is_primary_key] AS [主键],[c].[name] AS [索引列],ic.[is_descending_key] AS [逆序排列]
FROM
    [sys].[indexes] [id]
JOIN [sys].[sysindexkeys] [sik] ON ([sik].[id] = [id].[object_id] AND [sik].[indid] = [id].[index_id])
JOIN [sys].[columns] [c] ON ([c].[object_id] = [id].[object_id] AND [c].[column_id] = [sik].[colid])
JOIN [sys].[index_columns] ic ON ([ic].[index_id] = [id].[index_id] AND ic.[column_id]=c.[column_id] AND ic.[object_id]=c.[object_id])
WHERE
    [id].[object_id] = OBJECT_ID(@TableName)
ORDER BY [id].[name] ASC

SELECT [fk].[name] AS [外键名称],[SubCol].[name] AS [列名],
    [oMain].[name] AS [引用表],[MainCol].[name] AS [引用列名]
FROM
    [sys].[foreign_keys] [fk]
JOIN [sys].[all_objects] [oSub] ON ([fk].[parent_object_id] = [oSub].[object_id])
JOIN [sys].[all_objects] [oMain] ON ([fk].[referenced_object_id] = [oMain].[object_id])
JOIN [sys].[foreign_key_columns] [fkCols] ON ([fk].[object_id] = [fkCols].[constraint_object_id])
JOIN [sys].[columns] [SubCol] ON ([oSub].[object_id] = [SubCol].[object_id] AND [fkCols].[parent_column_id] = [SubCol].[column_id])
JOIN [sys].[columns] [MainCol] ON ([oMain].[object_id] = [MainCol].[object_id] AND [fkCols].[referenced_column_id] = [MainCol].[column_id])
WHERE oSub.[object_id]=OBJECT_ID(@TableName)
order by [fk].[name] ASC;


SELECT [chk].[name] AS [约束名],[col].[name] AS [列名],
    [chk].[definition] AS [约束条件]
FROM
    [sys].[check_constraints] [chk]
JOIN [sys].[columns] [col] ON ([chk].[parent_object_id] = [col].[object_id] AND [chk].[parent_column_id] = [col].[column_id])
WHERE [chk].[parent_object_id]=OBJECT_ID(@TableName)
ORDER BY col.[name] asc

SELECT  
        a.name AS [触发器名称] , 
        c.text AS [触发器语句]  
FROM    sys.sysobjects a  
        INNER JOIN sys.triggers b ON b.object_id = a.id  
        INNER JOIN sys.syscomments c ON c.id = a.id  
WHERE   a.xtype = 'tr'  AND a.[parent_obj]=OBJECT_ID(@TableName);

SELECT c.[name] AS 字段 ,t.[name] AS 类型, c.[prec] AS 长度,c.[scale] AS 精度,c.[isnullable] AS 可空
,COLUMNPROPERTY(c.[id],c.[name],'IsIdentity') AS 标识规范
,IDENT_SEED (tbl.[name]) AS 标识种子
,IDENT_INCR([tbl].[name]) AS 标识增量
FROM sys.[syscolumns] c
JOIN sys.[tables] tbl ON tbl.[object_id]=c.[id]
LEFT JOIN sys.[types] t ON t.[user_type_id]=c.[xusertype]
WHERE c.[id]=OBJECT_ID(@TableName) AND t.[name] !='sysname'
ORDER BY c.[name] asc

SELECT c.[name] AS 字段,sep.[name] AS 属性名,sep.[value] AS 属性值
FROM  sys.extended_properties sep 
JOIN [sys].[columns] c ON (c.[column_id]=[sep].[minor_id] AND [sep].[major_id]=c.[object_id])
WHERE sep.[major_id]=OBJECT_ID(@TableName)
ORDER BY c.[name] asc

SELECT [chk].[name] AS [约束名],[col].[name] AS [列名],
    [chk].[definition] AS [约束条件]
FROM
    [sys].default_constraints [chk]
JOIN [sys].[columns] [col] ON ([chk].[parent_object_id] = [col].[object_id] AND [chk].[parent_column_id] = [col].[column_id])
WHERE [col].[object_id]=OBJECT_ID(@TableName)
ORDER BY col.[name] asc
";

            }
            #endregion
            using (IDbCommand cmd = Connection.CreateCommand())
            {
                cmd.CommandText = _QUERY_TABLE;
                IDbDataParameter par = cmd.CreateParameter();
                par.ParameterName = "TableName";
                par.Value = _TableName;
                cmd.Parameters.Add(par);
                using (IDataReader reader = cmd.ExecuteReader())
                {
                    DataSet set = DbHelper.DataReaderToDataSet(reader);
                    return _Create表结构(set);
                }
            }
        }

        static 表结构 _Create表结构(DataSet set)
        {
            表结构 tb = new 表结构();
            {
                DataTable dt = set.Tables[0];
                tb._Exists = dt.Rows.Cast<DataRow>().Select(x => (int)x[0] > 0).FirstOrDefault();
            }
            {
                DataTable dt = set.Tables[1];
                var list = dt.Rows.Cast<DataRow>().Select(x => x.ChangeTo<表属性>()).ToList();
                tb.属性.AddRange(list);
            }
            {
                DataTable dt = set.Tables[2];
                var list = dt.Rows.Cast<DataRow>().Select(x => x.ChangeTo<索引>()).ToList();
                tb.索引.AddRange(list);
            }
            {
                DataTable dt = set.Tables[3];
                var list = dt.Rows.Cast<DataRow>().Select(x => x.ChangeTo<外键>()).ToList();
                tb.外键.AddRange(list);
            }
            {
                DataTable dt = set.Tables[4];
                var list = dt.Rows.Cast<DataRow>().Select(x => x.ChangeTo<约束>()).ToList();
                tb.检查约束.AddRange(list);
            }
            {
                DataTable dt = set.Tables[5];
                var list = dt.Rows.Cast<DataRow>().Select(x => x.ChangeTo<触发器>()).ToList();
                tb.触发器.AddRange(list);
            }
            {
                DataTable dt = set.Tables[6];
                var list = dt.Rows.Cast<DataRow>().Select(x => x.ChangeTo<表的列>()).ToList();
                tb.列.AddRange(list);
            }
            {
                DataTable dt = set.Tables[7];
                var list = dt.Rows.Cast<DataRow>().Select(x => x.ChangeTo<表的列属性>()).ToList();
                tb.列属性.AddRange(list);
            }
            {
                DataTable dt = set.Tables[8];
                var list = dt.Rows.Cast<DataRow>().Select(x => x.ChangeTo<约束>()).ToList();
                tb.默认值约束.AddRange(list);
            }
            return tb;
        }

        /// <summary>
        /// 已存在的源表数据，大写！
        /// </summary>
        [NonSerialized]
        public List<String> _ExistsSourceData = null;

        /// <summary>
        /// 已存在于目标表的数据，大写！
        /// </summary>
        [NonSerialized]
        public List<String> _ExistsTargetData = null;

        /// <summary>
        /// 计算映射并暂存到_Mapping
        /// </summary>
        internal void _InitMapping()
        {
            _Mapping = new List<MyTuple<Entities.表的列, Entities.表的列, 字段映射>>();
            if (字段映射 == null || 字段映射.Count == 0 || !_目标表._Exists)
            {
                #region 初始化全部映射
                for (int i = 0; i < _源表.列.Count; i++)
                {
                    var source = _源表.列[i];
                    表的列 target = null;
                    if (!_目标表._Exists)
                    {
                        target = source.ChangeTo<表的列>();
                        target.字段 = string.Concat(MyDbConfig.Current.新增字段前缀, source.字段);
                    }
                    else
                    {
                        target = _目标表.列
                       //.Where(x => !_目标表.索引.Any(y => y.主键 && string.Equals(y.索引列, x.字段, StringComparison.CurrentCultureIgnoreCase)))
                       .FirstOrDefault(x =>
                        String.Equals(x.字段, source.字段, StringComparison.CurrentCultureIgnoreCase)
                            || String.Equals(string.Concat(MyDbConfig.Current.新增字段前缀, source.字段), x.字段, StringComparison.CurrentCultureIgnoreCase));
                    }
                    if (target != null)
                    {
                        var fd = 字段映射.FirstOrDefault(x => string.Equals(x.新数据库字段, target.字段));
                        if (fd == null)
                        {
                            字段映射 mp = new 字段映射()
                            {
                                新数据库字段 = target.字段
                                 ,
                                旧数据库字段 = source.字段
                            };
                            字段映射.Add(mp);
                            _Mapping.Add(new MyTuple<Entities.表的列, Entities.表的列, 字段映射>(source, target, mp));
                        }
                        else
                        {
                            _Mapping.Add(new MyTuple<Entities.表的列, Entities.表的列, 字段映射>(source, target, fd));
                        }
                    }
                    else
                    {
                        //debugger;
                    }
                }
                #endregion
            }
            else
            {
                #region 初始化映射
                for (int i = 0; i < 字段映射.Count; i++)
                {
                    var f = 字段映射[i];
                    var source = _源表.列.FirstOrDefault(x => String.Equals(x.字段, f.旧数据库字段, StringComparison.CurrentCultureIgnoreCase));
                    if (source == null)
                    {
                        if (string.Equals(f.新数据库字段, "_PK", StringComparison.CurrentCultureIgnoreCase))
                        {
                            string sourceName = string.IsNullOrEmpty(this.源表匹配项) ?
                               _GetMatcher(_源表, _源表名) : this.源表匹配项;
                            source = new 表的列()
                            {
                                字段 = sourceName
                                 ,
                                可空 = false
                                 ,
                                类型 = "nvarchar"
                                 ,
                                长度 = 500
                            };
                        }
                        else if (string.IsNullOrEmpty(f.旧数据库字段))
                        {
                            //主键
                        }
                        else if (f.旧数据库字段.Contains("+") || f.旧数据库字段.IndexOf("CONVERT", StringComparison.CurrentCultureIgnoreCase) >= 0)
                        {
                            //转换
                        }
                        else if (source == null)
                        {
                            //debugger;
                        }
                    }
                    var target = _目标表.列.FirstOrDefault(x => String.Equals(x.字段, f.新数据库字段, StringComparison.CurrentCultureIgnoreCase));
                    if (target == null)
                    {
                        //debugger;
                    }
                    _Mapping.Add(new MyTuple<表的列, 表的列, Entities.字段映射>(source, target, f));
                }
                #endregion
            }
            if (字段映射.Any(x => !string.IsNullOrEmpty(x.文件主键)))
            {
                #region 文件类型
                if (!字段映射.Any(x => string.Equals("_New_Id", x.新数据库字段, StringComparison.CurrentCultureIgnoreCase)))
                {
                    var f = new 字段映射()
                    {
                        新数据库字段 = "_New_Id",
                        禁止更新 = true
                    };
                    字段映射.Add(f);
                    var target = _目标表.列.FirstOrDefault(x => String.Equals(x.字段, f.新数据库字段, StringComparison.CurrentCultureIgnoreCase));
                    if (target == null)
                    {
                        target = new 表的列()
                        {
                            字段 = 目标表匹配项
                                 ,
                            可空 = true
                                 ,
                            类型 = "nvarchar"
                                 ,
                            长度 = 500
                        };
                        _Mapping.Add(new MyTuple<表的列, 表的列, Entities.字段映射>(null, target, f));
                    }
                }
                #endregion
            }
            if (string.IsNullOrEmpty(源表匹配项))
            {
                源表匹配项 = _GetMatcher(_源表, 源表名);
            }
            if (string.IsNullOrEmpty(目标表匹配项))
            {
                #region 目标表匹配项
                目标表匹配项 = "_PK";
                if (!字段映射.Any(x => x.新数据库字段 == 目标表匹配项))
                {
                    字段映射.Add(new 字段映射()
                    {
                        旧数据库字段 = string.Empty,
                        新数据库字段 = 目标表匹配项
                    });
                }
                if (!_Mapping.Any(x => x.Item3.旧数据库字段 == 目标表匹配项))
                {
                    var f = 字段映射.FirstOrDefault(x => x.新数据库字段 == 目标表匹配项);
                    _Mapping.Add(new MyTuple<表的列, 表的列, 字段映射>(new 表的列()
                    {
                        字段 = 源表匹配项
                                 ,
                        可空 = false
                                 ,
                        类型 = "nvarchar"
                                 ,
                        长度 = 500
                    }, new 表的列()
                    {
                        字段 = 目标表匹配项
                                 ,
                        可空 = false
                                 ,
                        类型 = "nvarchar"
                                 ,
                        长度 = 500
                    }, f));
                }
                //目标表匹配项 = _GetMatcher(_目标表, 目标表名);
                #endregion
            }
            for (int i = 0; i < _Mapping.Count; i++)
            {
                #region 检查映射
                var f = _Mapping[i];
                if (f.Item1 == null)
                {
                    f.Item1 = _源表.列.FirstOrDefault(x => String.Equals(x.字段, f.Item3.旧数据库字段, StringComparison.CurrentCultureIgnoreCase));
                }
                if (f.Item2 == null)
                {
                    f.Item2 = _目标表.列.FirstOrDefault(x => String.Equals(x.字段, f.Item3.新数据库字段, StringComparison.CurrentCultureIgnoreCase));
                }
                if (f.Item1 != null && f.Item2 == null)
                {
                    //新增列
                    f.Item2 = f.Item1.ChangeTo<表的列>();
                    f.Item2.字段 = f.Item3.新数据库字段;
                    _目标表.列.Add(f.Item2);
                    using (IDbCommand cmd = MyDbConfig.Current.TargetConnection.CreateCommand())
                    {
                        cmd.CommandText = string.Format(@"
IF NOT EXISTS(SELECT * FROM sys.columns WHERE name='{1}' AND object_id=OBJECT_ID('{0}'))
ALTER TABLE dbo.[{0}]
ADD {2}"
, _目标表名
, f.Item3.新数据库字段
, 表结构._Create_Column(this, f.Item1, f.Item3.新数据库字段, true)
);
                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception exp)
                        {
                            //debugger;
                            MyLogger.Current._错误(this.Id.ToString(), exp);
                        }
                    }
                }
                else if (f.Item1 == null && f.Item2 != null)
                {
                    //Id列
                }
                else if (f.Item1 == null || f.Item2 == null)
                {
                    //新增Id列
                    f.Item2 = new 表的列()
                    {
                        字段 = f.Item3.新数据库字段
                         ,
                        类型 = "nvarchar"
                         ,
                        长度 = 500
                    };
                    _目标表.列.Add(f.Item2);
                    using (IDbCommand cmd = MyDbConfig.Current.TargetConnection.CreateCommand())
                    {
                        cmd.CommandText = string.Format(@"
IF NOT EXISTS(SELECT * FROM sys.columns WHERE name='{1}' AND object_id=OBJECT_ID('{0}'))
ALTER TABLE dbo.[{0}]
ADD {2}"
, _目标表名
, f.Item3.新数据库字段
, 表结构._Create_Column(this, f.Item2, f.Item3.新数据库字段, true)
);
                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception exp)
                        {

                            //debugger;
                            MyLogger.Current._错误(this.Id.ToString(), exp);
                        }
                    }
                }
                #endregion
            }
            if (!string.IsNullOrEmpty(MyDbConfig.Current.新增字段前缀))
            {
                #region 自动映射新增字段,哪怕是第二次运行
                for (int i = 0; i < _源表.列.Count; i++)
                {
                    var col = _源表.列[i];
                    string colName = col.字段;
                    string newColName = string.Concat(MyDbConfig.Current.新增字段前缀, colName);
                    if (!_Mapping.Any(x => string.Equals(x.Item3.新数据库字段, newColName, StringComparison.CurrentCultureIgnoreCase)))
                    {
                        表的列 newCol = col.ChangeTo<表的列>();
                        newCol.字段 = newColName;
                        using (IDbCommand cmd = MyDbConfig.Current.TargetConnection.CreateCommand())
                        {
                            cmd.CommandText = string.Format(@"
IF NOT EXISTS(SELECT * FROM sys.columns WHERE name='{1}' AND object_id=OBJECT_ID('{0}'))
ALTER TABLE dbo.[{0}]
ADD {2}"
    , _目标表名
    , newColName
    , 表结构._Create_Column(this, col, newColName, true)
    );
                            try
                            {
                                cmd.ExecuteNonQuery();
                            }
                            catch (Exception exp)
                            {
                                //debugger;
                                MyLogger.Current._错误(this.Id.ToString(), exp);
                            }
                        }
                        _Mapping.Add(new MyTuple<表的列, 表的列, 字段映射>(col, newCol, new 字段映射()
                        {
                            新数据库字段 = newColName
                                    ,
                            旧数据库字段 = colName
                                    ,
                            _Exists = true
                        }));
                    }
                }
                #endregion
            }
            if (_Mapping.Count == 0)
                throw new Exception($"无法为映射关系{Id}找到合适的映射条件!");


        }

        /// <summary>
        /// 用于执行的SQL语句集合
        /// </summary>
        [NonSerialized]
        public MyTuple<List<string>, Dictionary<String, string>, Dictionary<String, string>, Dictionary<String, string>, System.Collections.Concurrent.ConcurrentBag<string>> _SQL = null;

        /// <summary>
        /// 初始化计算暂存量，用于重启任务
        /// </summary>
        public void Reset()
        {
            _目标表 = null;
            _源表 = null;
            _Mapping = null;
            _SQL =
            new MyTuple<List<string>, Dictionary<String, string>, Dictionary<String, string>, Dictionary<String, string>, System.Collections.Concurrent.ConcurrentBag<string>>(
                new List<string>()
                , new Dictionary<String, string>()
                , new Dictionary<String, string>()
                , new Dictionary<String, string>()
                , new System.Collections.Concurrent.ConcurrentBag<string>()
            );
        }

        /// <summary>
        /// 初始化SQL语句
        /// </summary>
        public void InitSQL(dynamic _operator)
        {
            MyDbConfig.Current._SetOperator(_operator);
            if (测试)
            {
                //debugger;
            }
            _InitMapping();
            if (!_目标表._Exists)
            {
                #region Create 
                _SQL.Item1.AddRange(_源表.CreateTable(目标表名, this, this._目标表));
                _SQL.Item1.AddRange(_源表.CreatePrimaryKey(目标表名, _目标表, false, this));
                _SQL.Item1.AddRange(_源表.CreateTableProperty(目标表名, _目标表, false, this));
                _SQL.Item1.AddRange(_源表.CreateFieldsProperty(目标表名, _目标表, false, this));
                if (string.IsNullOrEmpty(MyDbConfig.Current.新增字段前缀))
                {
                    _SQL.Item1.AddRange(_源表.CreateForeignKey(目标表名, _目标表, false));
                    _SQL.Item1.AddRange(_源表.CreateTrigger(目标表名, _目标表, false));
                    _SQL.Item1.AddRange(_源表.CreateCheckConstraints(目标表名, _目标表, false));
                    _SQL.Item1.AddRange(_源表.CreateDefaultConstraints(目标表名, _目标表, false));
                    _SQL.Item1.AddRange(_源表.CreateIndex(目标表名, _目标表, false, this));
                    _SQL.Item1.AddRange(_源表.CreateUniqueConstraints(目标表名, _目标表, false, this));
                }
                _目标表.列.AddRange(_源表.列.Select(x =>
                {
                    表的列 y = x.ChangeTo<表的列>();
                    y.字段 = string.Concat(MyDbConfig.Current.新增字段前缀, x.字段);
                    return y;
                }));
                _目标表.索引.AddRange(_源表.索引.Select(x =>
                {
                    var y = x.ChangeTo<索引>();
                    y.索引列 = string.Concat(MyDbConfig.Current.新增字段前缀, x.索引列);
                    return y;
                }));
                #endregion
            }
            else if (表存在动作 == TableExistsActionType.默认)
            {
                #region 默认动作 以前缀添加所有列，并为字段映射指定项逐一赋值
                _SQL.Item1.AddRange(_源表.CreateTable(目标表名, this, _目标表));
                _SQL.Item1.AddRange(_源表.CreateTableProperty(目标表名, _目标表, false, this));
                _SQL.Item1.AddRange(_源表.CreateFieldsProperty(目标表名, _目标表, false, this));
                #endregion
            }
            else
            {
                #region 覆盖
                if (表存在动作.HasFlag(TableExistsActionType.添加新列))
                {
                    _SQL.Item1.AddRange(_源表.CreateTable(目标表名, this, _目标表));
                    var toAdd = _源表.列
                        .Where(x => !_目标表.列.Any(y => y.字段 == x.字段 || y.字段 == string.Concat(MyDbConfig.Current.新增字段前缀, x.字段)))
                        .Select(x =>
                        {
                            表的列 y = x.ChangeTo<表的列>();
                            y.字段 = string.Concat(MyDbConfig.Current.新增字段前缀, x.字段);
                            return x;
                        })
                        .ToList();
                    _目标表.列.AddRange(toAdd);
                }
                if (string.IsNullOrEmpty(MyDbConfig.Current.新增字段前缀))
                {
                    #region 覆盖其他属性
                    if (表存在动作.HasFlag(TableExistsActionType.覆盖主键))
                    {
                        _SQL.Item1.AddRange(_源表.CreatePrimaryKey(目标表名, _目标表, true, this));
                        _目标表.索引.RemoveAll(x => x.主键);
                        _目标表.索引.AddRange(_源表.索引.Where(x => x.主键));
                    }
                    if (表存在动作.HasFlag(TableExistsActionType.覆盖外键))
                    {
                        _SQL.Item1.AddRange(_源表.CreateForeignKey(目标表名, _目标表, true));
                    }
                    else if (表存在动作.HasFlag(TableExistsActionType.添加新外键))
                    {
                        _SQL.Item1.AddRange(_源表.CreateForeignKey(目标表名, _目标表, false));
                    }
                    if (表存在动作.HasFlag(TableExistsActionType.覆盖索引))
                    {
                        _SQL.Item1.AddRange(_源表.CreateIndex(目标表名, _目标表, true, this));
                    }
                    else if (表存在动作.HasFlag(TableExistsActionType.添加新索引))
                    {
                        _SQL.Item1.AddRange(_源表.CreateIndex(目标表名, _目标表, false, this));
                    }
                    if (表存在动作.HasFlag(TableExistsActionType.覆盖检查约束))
                    {
                        _SQL.Item1.AddRange(_源表.CreateCheckConstraints(目标表名, _目标表, true));
                    }
                    else if (表存在动作.HasFlag(TableExistsActionType.添加新检查约束))
                    {
                        _SQL.Item1.AddRange(_源表.CreateCheckConstraints(目标表名, _目标表, false));
                    }
                    if (表存在动作.HasFlag(TableExistsActionType.覆盖唯一约束))
                    {
                        _SQL.Item1.AddRange(_源表.CreateUniqueConstraints(目标表名, _目标表, true, this));
                    }
                    else if (表存在动作.HasFlag(TableExistsActionType.添加新唯一约束))
                    {
                        _SQL.Item1.AddRange(_源表.CreateUniqueConstraints(目标表名, _目标表, false, this));
                    }
                    if (表存在动作.HasFlag(TableExistsActionType.覆盖默认值约束))
                    {
                        _SQL.Item1.AddRange(_源表.CreateDefaultConstraints(目标表名, _目标表, true));
                    }
                    else if (表存在动作.HasFlag(TableExistsActionType.添加默认值约束))
                    {
                        _SQL.Item1.AddRange(_源表.CreateDefaultConstraints(目标表名, _目标表, false));
                    }
                    if (表存在动作.HasFlag(TableExistsActionType.覆盖触发器))
                    {
                        _SQL.Item1.AddRange(_源表.CreateTrigger(目标表名, _目标表, true));
                    }
                    else if (表存在动作.HasFlag(TableExistsActionType.添加新触发器))
                    {
                        _SQL.Item1.AddRange(_源表.CreateTrigger(目标表名, _目标表, false));
                    }
                    #endregion
                }
                if (表存在动作.HasFlag(TableExistsActionType.覆盖额外属性))
                {
                    _SQL.Item1.AddRange(_源表.CreateTableProperty(目标表名, _目标表, true, this));
                    _SQL.Item1.AddRange(_源表.CreateFieldsProperty(目标表名, _目标表, true, this));
                }
                else if (表存在动作.HasFlag(TableExistsActionType.添加新额外属性))
                {
                    _SQL.Item1.AddRange(_源表.CreateTableProperty(目标表名, _目标表, false, this));
                    _SQL.Item1.AddRange(_源表.CreateFieldsProperty(目标表名, _目标表, false, this));
                }
                #endregion
            }

            #region 执行表结构变更
            {
                List<String> listSQL = _SQL.Item1;
                #region 新建表
                if (listSQL.Count > 0)
                {
                    for (int i = 0; i < listSQL.Count; i++)
                    {
                        using (IDbCommand cmd = MyDbConfig.Current.TargetConnection.CreateCommand())
                        {
                            cmd.CommandText = listSQL[i];
                            try
                            {
                                using (IDataReader reader = cmd.ExecuteReader())
                                {
                                    DataTable table = DbHelper.DataReaderToDataTable(reader);
                                }
                            }
                            catch (Exception exp)
                            {
                                throw new Exception(cmd.CommandText, exp);
                            }

                        }
                    }
                    _目标表 = Create表结构(目标表名, MyDbConfig.Current.TargetConnection);
                    _InitMapping();
                }

                #endregion
            }
            #endregion

            _InitSourceAndTargetData();

            _SQL.Item2 = (_源表.CreateInsert(目标表名, _目标表, this));
            _SQL.Item3 = (_源表.CreateDelete(目标表名, _目标表, this));
            _SQL.Item4 = (_源表.CreateUpdate(目标表名, _目标表, this));


            #region 剔除空的SQL语句
            _SQL.Item1.RemoveAll(x => string.IsNullOrEmpty(x));
            //_SQL.Item2.RemoveAll(x => string.IsNullOrEmpty(x));
            //_SQL.Item3.RemoveAll(x => string.IsNullOrEmpty(x));
            //_SQL.Item4.RemoveAll(x => string.IsNullOrEmpty(x));
            #endregion
        }

        /// <summary>
        /// 初始化主键
        /// </summary>
        public void _InitSourceAndTargetData()
        {
            MyDbConfig.Current.SetMessageForOperator(this, $"正在初始化源数据及目标数据！");
            this._ExistsSourceData = _Get_ExistsSourceData(this);

            //if (this._目标表._Exists && this.字段映射 != null && this.字段映射.Any(x => x.加密数据))
            //{

            //    List<string> cols = this.字段映射.Where(x => x.加密数据)
            //        .Select(x => $"{x.新数据库字段.ToUpper()}")
            //        .ToList();
            //    DataTable _sourceRows = null;
            //    using (IDbCommand cmd = MyDbConfig.Current.TargetConnection.CreateCommand())
            //    {
            //        cmd.CommandText = $"SELECT {string.Join(",", cols)},UPPER({this.目标表匹配项}) AS _PK FROM [{this.目标表名}]";
            //        try
            //        {
            //            using (IDataReader reader = cmd.ExecuteReader())
            //            {
            //                _sourceRows = DbHelper.DataReaderToDataTable(reader);
            //            }
            //        }
            //        catch (Exception exp)
            //        {
            //            throw new Exception(cmd.CommandText, exp);
            //        }
            //    }
            //    #region 列第一行加密过，则跳过该列
            //    for (int j = _sourceRows.Columns.Count; j > 0 && _sourceRows.Rows.Count > 0;)
            //    {
            //        DataRow myRow = _sourceRows.Rows[0];
            //        --j;
            //        var col = _sourceRows.Columns[j];
            //        if (!cols.Any(x => string.Equals(x, col.ColumnName)))
            //        {
            //            continue;
            //        }
            //        string val = myRow[col].ToString();
            //        if (string.IsNullOrEmpty(val) || RegexHelper.IsEncrypted(val))
            //        {
            //            continue;
            //        }
            //        cols.Remove(col.ColumnName);
            //    }
            //    #endregion
            //    if (cols.Count > 0)
            //    {
            //        List<string> builderSql = new List<string>();
            //        for (int i = _sourceRows.Rows.Count; i > 0;)
            //        {
            //            --i;
            //            MyDbConfig.Current.SetMessageForOperator(this, $"{i}/{_sourceRows.Rows.Count} 正在加密旧数据！");
            //            DataRow myRow = _sourceRows.Rows[i];
            //            List<string> builder = new List<string>();
            //            for (int j = _sourceRows.Columns.Count; j > 0;)
            //            {
            //                --j;
            //                var col = _sourceRows.Columns[j];
            //                if (!cols.Any(x => string.Equals(x, col.ColumnName, StringComparison.CurrentCultureIgnoreCase)))
            //                {
            //                    continue;
            //                }
            //                string val = myRow[col].ToString();
            //                if (string.IsNullOrEmpty(val) || RegexHelper.IsEncrypted(val))
            //                {
            //                    continue;
            //                }
            //                builder.Add($"[{col.ColumnName}]='{SecurityHelper.Encrypt(val)}'");
            //            }
            //            if (builder.Count > 0)
            //            {
            //                builderSql.Add($"UPDATE [{this.目标表名}] SET {string.Join(",", builder)} WHERE {this.目标表匹配项}='{表结构._Encode(myRow["_PK"].ToString()) }'");
            //            }
            //        }
            //        if (builderSql.Count > 0)
            //        {
            //            using (IDbCommand cmd = MyDbConfig.Current.TargetConnection.CreateCommand())
            //            {
            //                cmd.CommandText = string.Join(Environment.NewLine, builderSql);
            //                try
            //                {
            //                    cmd.CommandTimeout = 20000;
            //                    cmd.ExecuteNonQuery();
            //                }
            //                catch (Exception exp)
            //                {
            //                    throw new Exception(cmd.CommandText, exp);
            //                }
            //            }
            //        }
            //    }
            //}
            this._ExistsTargetData = _Get_ExistsTargetData(this);

        }
        List<string> _Get_ExistsSourceData(表映射 _map)
        {
            using (IDbCommand cmd = MyDbConfig.Current.SourceConnection.CreateCommand())
            {
                cmd.CommandText = $"SELECT UPPER({_map.源表匹配项}) FROM [{_map.源表名}]";
                try
                {
                    using (IDataReader reader = cmd.ExecuteReader())
                    {
                        DataTable dt = DbHelper.DataReaderToDataTable(reader);
                        return dt.Rows.Cast<DataRow>().Select(x => x[0].ToString())
                            .Where(x => !string.IsNullOrEmpty(x))
                            .Distinct()
                            .ToList();
                    }
                }
                catch (Exception exp)
                {
                    throw new Exception(cmd.CommandText, exp);
                }
            }
        }

        List<string> _Get_ExistsTargetData(表映射 _map)
        {
            using (IDbCommand cmd = MyDbConfig.Current.TargetConnection.CreateCommand())
            {
                if (string.Equals(_map.目标表匹配项, "_PK", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (!_map._目标表.列.Any(x => string.Equals(x.字段, "_PK", StringComparison.CurrentCultureIgnoreCase)))
                    {
                        cmd.CommandText = string.Format(@"
IF NOT EXISTS(SELECT * FROM sys.columns WHERE name='{1}' AND object_id=OBJECT_ID('{0}'))
ALTER TABLE dbo.[{0}]
ADD [{1}] NVARCHAR(500) NULL"
, _map.目标表名
, "_PK"
);
                        cmd.ExecuteNonQuery();
                    }
                }
                cmd.CommandText = $"SELECT UPPER({_map.目标表匹配项}) FROM [{_map.目标表名}]";
                try
                {
                    using (IDataReader reader = cmd.ExecuteReader())
                    {
                        DataTable dt = DbHelper.DataReaderToDataTable(reader);
                        return dt.Rows.Cast<DataRow>()
                            .Select(x => x[0].ToString())
                            .Where(x => !string.IsNullOrEmpty(x))
                            .Distinct()
                            .ToList();
                    }
                }
                catch (Exception exp)
                {
                    throw new Exception(cmd.CommandText, exp);
                }
            }
        }

        /// <summary>
        /// 获取主键
        /// </summary>
        /// <param name="_source"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        string _GetMatcher(表结构 _source, string TableName)
        {
            var list = _source.索引.Where(x => x.主键).ToList();
            if (list.Count > 0)
            {
                return string.Join(" + ", list.OrderBy(x => x.索引列).Select(x => $"ISNULL(CONVERT(NVARCHAR(200), [{x.索引列}]),'')"));
            }
            throw new Exception($"表[{TableName}]没有匹配项(主键、唯一键，或在配置[{Id}]中指定)");
        }

        public override string ToString()
        {
            return $"{源表名}=>{目标表名}";
        }

        //public System.Collections.Concurrent.ConcurrentBag<string> _Uploading_Files = new System.Collections.Concurrent.ConcurrentBag<string>();
        public int _Uploading_Files_Total = 0;
        public int _Uploading_Files_Done = 0;

    }




}
